﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
//

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Resources;
using System.Data;
using System.Data.Common;
using System.IO;
using Microsoft.Windows.Controls;
using System.Data.EffiProz;

namespace EffiProz.Explorer
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private EfzConnection _connection;
        private string _databaseUrl;
        private int _maxRows = 1000;
        private bool showSystemTables = false;
        private bool showSchema = true;
    
        public MainWindow()
        {
            InitializeComponent();
        }

        public void SetConnection(EfzConnection connection)
        {
            _connection = connection;
        }

        public void SetDatabaseUrl(string databaseUrl)
        {
            _databaseUrl = databaseUrl;
        }


        private void Window_Loaded(object sender, RoutedEventArgs e)
        {        
            CreateConnection();
            DirectRefreshTree();
        }

        private void CreateConnection()
        {
            Connect connectDlg = new Connect(this);
            bool? result = connectDlg.ShowDialog();
            if (result.HasValue && result.Value)
            {
                connectionStatus.Content = "Connected (" +_databaseUrl+")";
            }
            else
            {
                connectionStatus.Content = "Not Connected";

            }
        }

        private void MenuItem_Click(object sender, RoutedEventArgs e)
        {
            About dlg = new About();
            dlg.ShowDialog();
        }

        private void showCommandHelp(string[] help)
        {
            FlowDocument doc = new FlowDocument();
            Paragraph para = new Paragraph();
            para.Inlines.Add(new Run(help[0]));
            doc.Blocks.Add(para);
            queryRichTextBox.Document = doc;

            messagesTextBox.Text = help[1];
            messagesTextBox.Focus();

            tabControl1.SelectedIndex = 1;
        }

        private void selectMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.selectHelp);
        }

        private void insertMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.insertHelp);
        }

        private void updateMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.updateHelp);
        }

        private void deleteMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.deleteHelp);
        }

        private void createTableMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.createTableHelp);
        }

        private void dropTableMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.dropTableHelp);
        }

        private void createIndexMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.createIndexHelp);
        }

        private void dropIndexMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.dropIndexHelp);
        }

        private void commitMenuItem_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                executeSQL("Commit;");
                showCommandHelp(new String[] {
                    "", "COMMIT executed"
                });
            }
            catch (Exception ex)
            {
                messagesTextBox.Text = "Unable to Commit.";
                errorMessage(ex);
            }
           
        }

        private void rollbackMenuItem_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                executeSQL("Rollback;");
                showCommandHelp(new String[] {
                    "", "ROLLBACK executed"
                });
            }
            catch (Exception ex)
            {
                messagesTextBox.Text = "Unable to Rollback.";
                errorMessage(ex);
            }
        }

        private void checkpointMenuItem_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                executeSQL("Rollback;");
                showCommandHelp(new String[] {
                    "", "CHECKPOINT executed"
                });
            }
            catch (Exception ex)
            {
                messagesTextBox.Text = "Unable to Checkpoint.";
                errorMessage(ex);
            }

        }

        private void scriptMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.scriptHelp);
        }

        private void setMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.setHelp);
        }

        private void shutdownMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showCommandHelp(Utils.shutdownHelp);
        }


        private void errorMessage(Exception exceptionMsg)
        {
            messagesTextBox.Text = "Error: "+exceptionMsg.Message;
            messagesTextBox.Focus();

            tabControl1.SelectedIndex = 1;
        }

        private void openScriptMenuItem_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            dlg.FileName = ""; // Default file name
            dlg.DefaultExt = ".sql"; // Default file extension
            dlg.Filter = "EffiProz SQL Script File (.sql)|*.sql"; // Filter files by extension

            bool? result = dlg.ShowDialog();

            if (result == true)
            {
                try
                {
                    string scriptFile = dlg.FileName;

                    TextRange range = new TextRange(queryRichTextBox.Document.ContentStart, queryRichTextBox.Document.ContentEnd);

                    FileStream fStream = new System.IO.FileStream(scriptFile, System.IO.FileMode.Open);

                    range.Load(fStream, System.Windows.DataFormats.Text);

                    fStream.Close();


                    executeButton.IsEnabled = true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to read script file.\n Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                }
                
            }
        }

        private void saveScriptMenuItem_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            dlg.FileName = ""; // Default file name
            dlg.DefaultExt = ".sql"; // Default file extension
            dlg.Filter = "EffiProz SQL Script File (.sql)|*.sql"; // Filter files by extension

            bool? result = dlg.ShowDialog();
            
            if (result == true)
            {
                try
                {
                    string scriptFile = dlg.FileName;
                    using (TextWriter writer = new StreamWriter(new FileStream(scriptFile, FileMode.OpenOrCreate, FileAccess.Write)))
                    {

                        TextRange textRange = new TextRange(queryRichTextBox.Document.ContentStart,
                             queryRichTextBox.Document.ContentEnd);

                        writer.Write(textRange.Text);
                        writer.Flush();
                    }
                   
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to save script file.\n Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                }

            }
        }

        private void clearButton_Click(object sender, RoutedEventArgs e)
        {
            TextRange textRange = new TextRange(queryRichTextBox.Document.ContentStart,
                         queryRichTextBox.Document.ContentEnd);
            textRange.Text = "";

            executeButton.IsEnabled = true;
        }

        private void executeSQL(string sql)
        {
            if(_connection == null || _connection.State == ConnectionState.Closed)
                MessageBox.Show("No open connection.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);

            using (EfzCommand command = _connection.CreateCommand())
            {
                command.MaxRows = _maxRows;
                command.CommandText = sql;

                EfzDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    DataTable table = new DataTable();
                    table.Load(reader);
                    resultsDataGrid.DataContext = table;
                    messagesTextBox.Text = "Fetched " + table.Rows.Count + " records.";
                }
                else
                {
                    messagesTextBox.Text = "" + reader.RecordsAffected + " records affected.";
                }
            }

            
           
        }

        private void executeButton_Click(object sender, RoutedEventArgs e)
        {
            TextRange textRange = new TextRange(queryRichTextBox.Document.ContentStart,
                        queryRichTextBox.Document.ContentEnd);

            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();

            messagesTextBox.Text = "";
            resultsDataGrid.Columns.Clear();
            resultsDataGrid.DataContext = null;
            stopWatch.Start();
            try
            {
                executeSQL(textRange.Text);
            }
            catch (Exception ex)
            {                
                errorMessage(ex);
                messagesTextBox.Text = messagesTextBox.Text+Environment.NewLine+ "Failed to execute SQL command.";
                return;
            }
            stopWatch.Stop();

            messagesTextBox.Text = messagesTextBox.Text + Environment.NewLine + "SQL command sucessfully executed in " + stopWatch.ElapsedMilliseconds + " ms.";

        }

        private void MainWindow_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            CloseConnection();
            EfzConnection.ClearAllPools();
        }

        private void CloseConnection()
        {
            if (_connection != null)
            {
                try
                {
                    _connection.Close();
                }
                catch (Exception) { }
            }          
        }

        private void connectMenuItem_Click(object sender, RoutedEventArgs e)
        {
            CloseConnection();
            CreateConnection();
            messagesTextBox.Text = "";
            RefreshTree();
        }

        private void maxRows1000MenuItem_Click(object sender, RoutedEventArgs e)
        {
            if (maxRows1000MenuItem.IsChecked)
                _maxRows = 1000;
            else
                _maxRows = 0; 
        }

        private void autoCommitMenuItem_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if(autoCommitMenuItem.IsChecked)
                    _connection.SetAutoCommit(true);
                else
                    _connection.SetAutoCommit(false);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to set AutoCommit mode.\n Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }

        }

        private void saveResultsMenuItem_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DataTable dt = (DataTable)resultsDataGrid.DataContext;
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.FileName = ""; // Default file name
                dlg.DefaultExt = ".csv"; // Default file extension
                dlg.Filter = "Comma Seperated Values File (.csv)|*.csv"; // Filter files by extension

                bool? result = dlg.ShowDialog();

                if (result == true)
                {                   
                    StreamWriter sw = new StreamWriter(dlg.FileName, false);
                    
                    int iColCount = dt.Columns.Count;
                    for (int i = 0; i < iColCount; i++)
                    {
                        sw.Write(dt.Columns[i]);
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }

                    sw.Write(sw.NewLine);

                    foreach (DataRow dr in dt.Rows)
                    {
                        for (int i = 0; i < iColCount; i++)
                        {
                            if (!Convert.IsDBNull(dr[i]))
                            {
                                sw.Write(dr[i].ToString());
                            }

                            if (i < iColCount - 1)
                            {
                                sw.Write(",");
                            }
                        }

                        sw.Write(sw.NewLine);
                    }
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to save results.\n Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        protected void RefreshTree()
        {
            DirectRefreshTree();
        }

        protected void DirectRefreshTree()
        {
            
            databaseTreeView.Items.Clear();

            if (_connection == null || _connection.State != ConnectionState.Open)
                return;

            try
            {
                BitmapImage databaseImg = new BitmapImage();
                databaseImg.BeginInit();
                databaseImg.UriSource = new Uri("pack://application:,,/Resources/Databases.gif");
                databaseImg.EndInit();

                TreeViewItemWithImage rootNode = new TreeViewItemWithImage();
                rootNode.HeaderText = "Database ("+_databaseUrl+")";
                rootNode.Icon = databaseImg;

                databaseTreeView.Items.Add(rootNode);

                DataTable tables = _connection.GetSchema("Tables");

                TreeViewItemWithImage tableNode;
                BitmapImage tableImg = new BitmapImage();
                tableImg.BeginInit();
                tableImg.UriSource = new Uri("pack://application:,,/Resources/Tables.gif");
                tableImg.EndInit();
                BitmapImage viewImg = new BitmapImage();
                viewImg.BeginInit();
                viewImg.UriSource = new Uri("pack://application:,,/Resources/Views.gif");
                viewImg.EndInit();
                string tableName, schemaName, tableType;
                foreach (DataRow row in tables.Rows)
                {
                    tableType = (string)row["TABLE_TYPE"];
                    if (!tableType.Equals("TABLE") && !tableType.Equals("VIEW") &&
                        !(showSystemTables && tableType.Equals("SYSTEM TABLE")))
                        continue;

                    tableName = (string)row["TABLE_NAME"];
                    schemaName = (string)row["TABLE_SCHEM"];
                    tableNode = new TreeViewItemWithImage();
                    tableNode.HeaderText = showSchema ? schemaName + "." + tableName :
                        tableName;
                    tableNode.Icon = tableType.Equals("VIEW") || tableType.Equals("SYSTEM TABLE")
                        ? viewImg : tableImg;
                    rootNode.Items.Add(tableNode);
                }

                rootNode.IsExpanded = true;
            }
            catch (Exception) { }
        }

        internal class TreeViewItemWithImage : TreeViewItem
        {            
            ImageSource iconSource;
            TextBlock textBlock;
            Image icon;           

            public TreeViewItemWithImage()
            {
                StackPanel stack = new StackPanel();
                stack.Orientation = Orientation.Horizontal;
                Header = stack;               
                icon = new Image();
                icon.VerticalAlignment = VerticalAlignment.Center;
                icon.Margin = new Thickness(0, 0, 4, 0);
                icon.Source = iconSource;
                stack.Children.Add(icon);            
                textBlock = new TextBlock();
                textBlock.VerticalAlignment = VerticalAlignment.Center;
                stack.Children.Add(textBlock);
            }
           
            public ImageSource Icon
            {
                set
                {
                    iconSource = value;
                    icon.Source = iconSource;
                }
                get
                {
                    return iconSource;
                }
            }
        
            protected override void OnUnselected(RoutedEventArgs args)
            {
                base.OnUnselected(args);
                icon.Source = iconSource;
            }
         
            protected override void OnSelected(RoutedEventArgs args)
            {
                base.OnSelected(args);
                icon.Source = iconSource;
            }

          
            public string HeaderText
            {
                set
                {
                    textBlock.Text = value;
                }
                get
                {
                    return textBlock.Text;
                }
            }           
        }

        private void exitMenuItem_Click(object sender, RoutedEventArgs e)
        {           
            this.Close();
        }

        private void refreshTreeMenuItem_Click(object sender, RoutedEventArgs e)
        {
            RefreshTree();
        }

        private void showSchemaMenuItem_Click(object sender, RoutedEventArgs e)
        {
            showSchema = showSchemaMenuItem.IsChecked;
            RefreshTree();
        }

        private void showSystemTablesManuItem_Click(object sender, RoutedEventArgs e)
        {
            showSystemTables = showSystemTablesManuItem.IsChecked;
            RefreshTree();
        }

        private void queryRichTextBox_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (queryRichTextBox.Document == null) 
                return;

            TextRange documentRange = new TextRange(queryRichTextBox.Document.ContentStart, 
                queryRichTextBox.Document.ContentEnd); 
            documentRange.ClearAllProperties();

            TextPointer navigator = queryRichTextBox.Document.ContentStart;
            while (navigator.CompareTo(queryRichTextBox.Document.ContentEnd) < 0)    
            {                
                TextPointerContext context = navigator.GetPointerContext(LogicalDirection.Backward); 
                if (context == TextPointerContext.ElementStart && navigator.Parent is Run)    
                {                   
                    CheckWordsInRun((Run)navigator.Parent);  
                }                
                navigator = navigator.GetNextContextPosition(LogicalDirection.Forward);       
            }

            Format();
        }

        new struct Tag
        {
            public TextPointer StartPosition;
            public TextPointer EndPosition;
            public string Word;

        }

        List<Tag> m_tags = new List<Tag>();

        void Format()
        {
            queryRichTextBox.TextChanged -= this.queryRichTextBox_TextChanged;

            for (int i = 0; i < m_tags.Count; i++)
            {
                TextRange range = new TextRange(m_tags[i].StartPosition, m_tags[i].EndPosition);
                range.ApplyPropertyValue(TextElement.ForegroundProperty, new SolidColorBrush(Colors.Blue));
                range.ApplyPropertyValue(TextElement.FontWeightProperty, FontWeights.Bold);
            }
            m_tags.Clear();

            queryRichTextBox.TextChanged += this.queryRichTextBox_TextChanged;
        }

        void CheckWordsInRun(Run run)
        {
            string text = run.Text;

            int sIndex = 0;
            int eIndex = 0;
            for (int i = 0; i < text.Length; i++)
            {
                if (Char.IsWhiteSpace(text[i]) | SqlSyntaxProvider.GetSpecials.Contains(text[i]))
                {
                    if (i > 0 && !(Char.IsWhiteSpace(text[i - 1]) | SqlSyntaxProvider.GetSpecials.Contains(text[i - 1])))
                    {
                        eIndex = i - 1;
                        string word = text.Substring(sIndex, eIndex - sIndex + 1);

                        if (SqlSyntaxProvider.IsKnownTag(word))
                        {
                            Tag t = new Tag();
                            t.StartPosition = run.ContentStart.GetPositionAtOffset(sIndex, LogicalDirection.Forward);
                            t.EndPosition = run.ContentStart.GetPositionAtOffset(eIndex + 1, LogicalDirection.Backward);
                            t.Word = word;
                            m_tags.Add(t);
                        }
                    }
                    sIndex = i + 1;
                }
            }

            string lastWord = text.Substring(sIndex, text.Length - sIndex);
            if (SqlSyntaxProvider.IsKnownTag(lastWord))
            {
                Tag t = new Tag();
                t.StartPosition = run.ContentStart.GetPositionAtOffset(sIndex, LogicalDirection.Forward);
                t.EndPosition = run.ContentStart.GetPositionAtOffset(eIndex + 1, LogicalDirection.Backward);
                t.Word = lastWord;
                m_tags.Add(t);
            }
        }

        private void disconnectMenuItem_Click(object sender, RoutedEventArgs e)
        {
            CloseConnection();            
            messagesTextBox.Text = "";
            resultsDataGrid.Columns.Clear();
            resultsDataGrid.DataContext = null;
            RefreshTree();       
            connectionStatus.Content = "Not Connected";
        }

        private void Window_Closed(object sender, EventArgs e)
        {
            Environment.Exit(0);
        }    

    }
}